﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using Ledict.ConditionExtract.Common;
using Ledict.Utility.Extension;

namespace Ledict.ConditionExtract.DAL
{
    public class XmlLabel_SuitTypeRelDAL
    {
        private string DbName;
        //private string CommonDbName;
        public XmlLabel_SuitTypeRelDAL()
        {
            DbName = "JudgeDoc";
            //CommonDbName = "LdtCommonManage";
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string CC_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from SYS_XmlLabel_SuitTypeRel");
            strSql.Append(" where LabelID=@CC_ID ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, CC_ID);

            return Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0 ? true : false;


        }

        /// <summary>
        /// 获得诉请种类与各要素关系数据列表，根据诉请种类ID
        /// </summary>
        /// <param name="stId">诉请种类ID</param>
        public DataTable GetSuitTypeRelList(string stId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@" select *
                                           ,case when IsShowLabel = 1 then '显示' else '不显示' end as IsShowLabelCn,CONVERT(bit,IsShowLabel) as IsShowLableDisplay 
                                           from SYS_XmlLabel_SuitTypeRel where st_id = @stId order by charindex(LabelType,'SuitFactReason'),ShowIndex "));

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@stId", DbType.String, stId);

            DataTable dt = oBase.ExecuteDataSet(oCmd).Tables[0];

            return dt;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(SYS_XmlLabel_SuitTypeRel Model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SYS_XmlLabel_SuitTypeRel(");
            strSql.Append("XSR_ID,ST_ID,LabelID,LabelType,LabelXpath)");
            strSql.Append(" values (");
            strSql.Append("@XSR_ID,@ST_ID,@LabelID,@LabelType,@LabelXpath)");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@XSR_ID", DbType.String, Model.XSR_ID);
            oBase.AddInParameter(oCmd, "@ST_ID", DbType.String, Model.ST_ID);
            oBase.AddInParameter(oCmd, "@LabelID", DbType.String, Model.LabelID);
            oBase.AddInParameter(oCmd, "@LabelType", DbType.String, Model.LabelType);
            oBase.AddInParameter(oCmd, "@LabelXpath", DbType.String, Model.LabelXpath);


            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 批量添加数据
        /// </summary>
        public bool BatchAdd(IList<SYS_XmlLabel_SuitTypeRel> list)
        {
            StringBuilder strSql = new StringBuilder();
            foreach (var item in list)
            {
                string strShowIndex;
                if (item.ShowIndex.HasValue)
                    strShowIndex = item.ShowIndex.ToString();
                else
                    strShowIndex = "null";



                strSql.Append(string.Format(@"  INSERT INTO [dbo].[SYS_XmlLabel_SuitTypeRel]
           ([XSR_ID]
           ,[ST_ID]
           ,[LabelID]
           ,[LabelType]
           ,[LabelXpath]
           ,[IsShowLabel]
           ,[ShowIndex])
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,{6})  ", item.XSR_ID, item.ST_ID, item.LabelID, item.LabelType, item.LabelXpath, item.IsShowLabel ? 1 : 0, strShowIndex));

            }

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            if (oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString()) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获得诉请种类数据列表，根据情形情节ID
        /// </summary>
        /// <param name="ccId">情形情节ID</param>
        /// <param name="searchWords">查询关键词</param>
        public DataTable GetSuitTypeList(string ccId, string searchWords = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@"  select * from SYS_SuitType where CaseCauseID = @CaseCauseID  "));

            //有关键词则，则加入关键词搜索条件
            if (!string.IsNullOrEmpty(searchWords))
            {
                strSql.Append(string.Format(@" and SuitTypeName like '%{0}%'", searchWords));
            }
            strSql.Append(" order by convert(int,SuitTypecode) ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@CaseCauseID", DbType.String, ccId);

            DataTable dt = oBase.ExecuteDataSet(oCmd).Tables[0];

            return dt;
        }

        /// <summary>
        /// 更新suittype表
        /// </summary>
        /// <param name="ccId">情形情节ID</param>
        /// <param name="suitTypecode">类型编码</param>
        /// <param name="suitTypeName">类型名称</param>
        /// <returns></returns>
        public bool Update_SYS_SuitType_suitTypecode(string ccId, string suitTypecode, string suitTypeName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" Update SYS_SuitType set SuitTypeName = @SuitTypeName where SuitTypecode = @SuitTypecode and CaseCauseID = @CaseCauseID");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@CaseCauseID", DbType.String, ccId);
            oBase.AddInParameter(oCmd, "@SuitTypecode", DbType.String, suitTypecode);
            oBase.AddInParameter(oCmd, "@SuitTypeName", DbType.String, suitTypeName);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 添加suittype表
        /// </summary>
        /// <param name="ccId">情形情节ID</param>
        /// <param name="suitTypecode">类型编码</param>
        /// <param name="suitTypeName">类型名称</param>
        /// <returns></returns>
        public bool Add_SYS_SuitType_suitTypecode(string ccId, string suitTypecode, string suitTypeName)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" INSERT INTO [dbo].[SYS_SuitType]
           ([ST_ID]
           ,[CaseCauseID]
           ,[SuitTypecode]
           ,[SuitTypeName]
           ,[UpdateTime])
     VALUES
           (@ST_ID
           ,@CaseCauseID
           ,@SuitTypecode
           ,@SuitTypeName
           ,@UpdateTime)");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@ST_ID", DbType.String, Guid.NewGuid().ToString());
            oBase.AddInParameter(oCmd, "@CaseCauseID", DbType.String, ccId);
            oBase.AddInParameter(oCmd, "@SuitTypecode", DbType.String, suitTypecode);
            oBase.AddInParameter(oCmd, "@SuitTypeName", DbType.String, suitTypeName);
            oBase.AddInParameter(oCmd, "@UpdateTime", DbType.DateTime, DateTime.Now);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 删除suittype数据
        /// </summary>
        /// <param name="stId">ID</param>
        /// <returns></returns>
        public bool Delete_SYS_SuitType_suitTypecode(string stId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" delete from SYS_SuitType where ST_ID = @ST_ID ");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@ST_ID", DbType.String, stId);
            
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获得诉请种类关系数据列表，根据情形情节ID
        /// </summary>
        /// <param name="ccId">情形情节ID</param>
        /// <param name="labelType">标签类型</param>
        /// <param name="isLabelPathDistinct">是否标签路径去重</param>
        public DataTable GetSuitTypeRelListByCcid(string ccId, string labelType = null, bool isLabelPathDistinct = false)
        {
            StringBuilder strSql = new StringBuilder();
            //标签路径去重
            if (isLabelPathDistinct)
            {
                strSql.Append(@"  select distinct c.LabelXpath,c.XSR_ID,c.ST_ID,c.LabelID,c.LabelType,c.IsShowLabel,c.UpdateTime,c.ShowIndex,c.SuitTypeName, CONVERT(bit,c.IsShowLabel) as IsShowLableDisplay from (
	                                select a.*,b.SuitTypeName from [SYS_XmlLabel_SuitTypeRel] as a
	                                inner join [dbo].[SYS_SuitType] as b  
	                                on a.ST_ID = b.ST_ID and b.CaseCauseID = @CaseCauseID ) as c
                                               ");

            }
            else
            {
                strSql.Append(@" select *,CONVERT(bit,c.IsShowLabel) as IsShowLableDisplay from (
                                              select a.*,b.SuitTypeName from [SYS_XmlLabel_SuitTypeRel] as a
                                              inner join [dbo].[SYS_SuitType] as b  
                                              on a.ST_ID = b.ST_ID and b.CaseCauseID = @CaseCauseID ) as c
                                               ");

            }
            //标签类型
            if (!string.IsNullOrEmpty(labelType))
                strSql.Append("  where c.LabelType = '" + labelType + "' ");
            strSql.Append("  order by c.SuitTypeName desc,LabelType ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@CaseCauseID", DbType.String, ccId);

            DataTable dt = oBase.ExecuteDataSet(oCmd).Tables[0];

            return dt;
        }

        /// <summary>
        /// 批量删除节点
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteBatchByXSR_ID(IList<string> idList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@" delete from SYS_XmlLabel_SuitTypeRel where XSR_ID in ({0})", idList.ListToStringBySign(",", "'")));
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            return oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 修改指定ID下的显示顺序
        /// </summary>
        /// <param name="xsrId">主键</param>
        /// <param name="index">显示顺序</param>
        /// <returns></returns>
        public int UpdateIndex(string xsrId, string index)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@" update SYS_XmlLabel_SuitTypeRel set ShowIndex = {1} where XSR_ID = '{0}'", xsrId, index));
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            return oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 修改指定ID下的XPath标签路径
        /// </summary>
        /// <param name="xsrId">主键</param>
        /// <param name="xPath">XPath标签路径</param>
        /// <returns></returns>
        public int UpdateXPath(string xsrId, string xPath)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@" update SYS_XmlLabel_SuitTypeRel set LabelXpath = '{1}' where XSR_ID = '{0}'", xsrId, xPath));
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            return oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 修改指定ID下的显示顺序，xpath标签路径
        /// </summary>
        /// <param name="xsrId">ID</param>
        /// <param name="xPath">标签路径</param>
        /// <param name="index">显示顺序</param>
        /// <returns></returns>
        public int UpdateIndexAndXPath(string xsrId, string xPath, string index)
        {
           string strSql=string.Format(@" update SYS_XmlLabel_SuitTypeRel set LabelXpath = '{0}',ShowIndex = {1} where XSR_ID = '{2}'",xPath, string.IsNullOrEmpty(index.Trim()) ? "null" : index, xsrId);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            return oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }


        /// <summary>
        /// 修改指定情形情节ID下的同一种诉请要素种类下，同一个标签路径的显示顺序
        /// </summary>
        /// <param name="labelType">诉请要素类型</param>
        /// <param name="index">显示顺序</param>
        /// <param name="ccid">情形情节ID</param>
        /// <param name="labelPath">标签路径</param>
        /// <returns>影响行数</returns>
        public int UpdateIndex(string ccid, string labelPath, string labelType, int index)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@" update [SYS_XmlLabel_SuitTypeRel]
	                                        set ShowIndex = {0}
	                                        where LabelXpath = '{1}' and LabelType  = '{2}' and XSR_ID in (
	                                         select XSR_ID from [SYS_XmlLabel_SuitTypeRel] as a
	                                         inner join [SYS_SuitType] as b on a.ST_ID = b.ST_ID and b.CaseCauseID = '{3}'
	                                        )", index, labelPath, labelType, ccid));
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            return oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 修改指定ID下的是否显示标签
        /// </summary>
        /// <param name="xsrId">主键</param>
        /// <param name="isShowLabel">是否显示标签</param>
        /// <returns></returns>
        public int UpdateIsShowLabel(string xsrId, bool isShowLabel)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@" update SYS_XmlLabel_SuitTypeRel set isShowLabel = '{1}' where XSR_ID = '{0}'", xsrId, Convert.ToInt32(isShowLabel)));
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            return oBase.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }
    }
}
